﻿using System;
using System.Linq;
using Orchard.ContentManagement;
using Orchard.ContentManagement.Drivers;
using Orchard.ContentManagement.MetaData;
using Orchard.ContentManagement.MetaData.Builders;
using Orchard.Core.Contents.Extensions;
using Orchard.Data.Migration;
using NineSeas.Models;
using Orchard;
using Orchard.Core.Common.Models;
using Orchard.Services;
using Orchard.Data;
using NineSeas.Handlers;
using Orchard.Tags.Models;
using NineSeas.Services;
using NineSeas.Extensions;
using Contrib.Taxonomies.Services;
using System.Text.RegularExpressions;
using Orchard.PublishLater.Models;

namespace NineSeas
{
    public class Migrations : DataMigrationImpl
    {
        private readonly IOrchardServices _services;
        private readonly IClock _clock;
        private readonly ISessionLocator _sessionLocator;
        private readonly IRepository<AuthorProfilePartRecord> _authorProfileRepository;
        private readonly IRepository<BookPartRecord> _bookRepository;
        private readonly IRepository<CommonPartRecord> _commonPartRepository;
        private readonly IRepository<CommonPartVersionRecord> _commonPartVersionRepository;
        private readonly IWeekCalculateTaskHandler _calculateTask;
        private readonly ITaxonomyImportService _taxonomyImportService;
        private readonly ITaxonomyService _taxonomyService;

        public Migrations(IOrchardServices services,
            IClock clock,
            ISessionLocator sessionLocator,
            IRepository<AuthorProfilePartRecord> authorProfileRepository,
            IRepository<BookPartRecord> bookRepository,
            IRepository<CommonPartRecord> commonPartRepository,
            IRepository<CommonPartVersionRecord> commonPartVersionRepository,
            IWeekCalculateTaskHandler calculateTask,
            ITaxonomyImportService taxonomyImportService,
            ITaxonomyService taxonomyService)
        {
            _clock = clock;
            _services = services;
            _sessionLocator = sessionLocator;
            _authorProfileRepository = authorProfileRepository;
            _bookRepository = bookRepository;
            _commonPartRepository = commonPartRepository;
            _commonPartVersionRepository = commonPartVersionRepository;
            _calculateTask = calculateTask;
            _taxonomyImportService = taxonomyImportService;
            _taxonomyService = taxonomyService;
        }
        public int Create()
        {
            SchemaBuilder.CreateTable("AuthorProfilePartRecord", table => table
                    .ContentPartRecord()
                    .Column<string>("DisplayName", c => c.WithLength(255))
                    .Column<string>("StatusMessage", c => c.WithLength(255))
                    .Column<string>("Phone", c => c.WithLength(255))
                    .Column<string>("BannerColor", c => c.WithLength(50))
                    .Column<DateTime>("DateOfBirth", c => c.Nullable())
                );

            ContentDefinitionManager.AlterTypeDefinition("User", type => type
               .WithPart("AuthorProfilePart"));
            return 1;
        }
        public int UpdateFrom1()
        {
            SchemaBuilder.AlterTable("AuthorProfilePartRecord", table => table.DropColumn("DateOfBirth"));
            SchemaBuilder.AlterTable("AuthorProfilePartRecord", table => table.AddColumn<string>("DateOfBirth", c => c.WithLength(50)));
            return 2;
        }

        public int UpdateFrom2()
        {
            SchemaBuilder.CreateTable("BookPartRecord", table => table
                    .ContentPartRecord()
                    .Column<int>("AuthorId")
                    .Column<string>("Name", c => c.WithLength(255))
                    .Column<string>("NameOnUrl", c => c.WithLength(255))
                    .Column<string>("Introduction", c => c.WithLength(4000))
                    .Column<DateTime>("CreatedDate", c => c.Nullable())
                    .Column<DateTime>("LastUpdatedDate", c => c.Nullable())
                );
            
            SchemaBuilder.CreateTable("ChapterPartRecord", table => table
                    .ContentPartRecord()
                    .Column<int>("BookId")
                    .Column<string>("Title", c => c.WithLength(255))
                    .Column<string>("Content", c => c.Unlimited())
                );

            SchemaBuilder.CreateForeignKey("Chapter_Book", "ChapterPartRecord", new[] { "BookId" }, "BookPartRecord", new[] { "Id" });

            ContentDefinitionManager.AlterTypeDefinition("Book", t => t
                .WithPart(typeof(BookPart).Name)
                );

            ContentDefinitionManager.AlterTypeDefinition("Chapter", t => t
                .WithPart(typeof(ChapterPart).Name)
                );

            ContentDefinitionManager.AlterPartDefinition("BookPart",
                    builder => builder.Attachable(false));

            ContentDefinitionManager.AlterPartDefinition("ChapterPart",
                    builder => builder.Attachable(false));
            return 3;
        }
        public int UpdateFrom3()
        {
            SchemaBuilder.AlterTable("ChapterPartRecord", table => table.AddColumn<int>("SortOrder"));
            return 4;
        }
        public int UpdateFrom4()
        {
            SchemaBuilder.AlterTable("AuthorProfilePartRecord", table => table.AddColumn<string>("AvatarUrl", c => c.WithLength(255)));
            SchemaBuilder.AlterTable("BookPartRecord", table => table.AddColumn<string>("ImageUrl", c => c.WithLength(255)));

            return 5;
        }
        public int UpdateFrom5()
        {
            SchemaBuilder.AlterTable("AuthorProfilePartRecord", table => table.AddColumn<string>("AvatarStoragePath", c => c.WithLength(255)));
            SchemaBuilder.AlterTable("BookPartRecord", table => table.AddColumn<string>("ImageStoragePath", c => c.WithLength(255)));
            return 6;
        }
        public int UpdateFrom6()
        {
            SchemaBuilder.AlterTable("BookPartRecord", table => table.AddColumn<int>("TotalViews", c => c.WithDefault(0).NotNull()));
            SchemaBuilder.AlterTable("BookPartRecord", table => table.AddColumn<int>("TotalLikes", c => c.WithDefault(0).NotNull()));
            return 7;
        }

        public int UpdateFrom7()
        {
            SchemaBuilder.CreateTable("NineSeasSettingsPartRecord", table => table
                    .ContentPartRecord()
                    .Column<int>("FbCommentsNumberOfPosts")
                    .Column<int>("FbCommentsWidth")
                    .Column<string>("FbCommentsColorScheme", c => c.WithLength(255))
                    .Column<int>("FbLikesWidth")
                    .Column<string>("FbLikesColorScheme", c => c.WithLength(255))
                    .Column<bool>("FbLikesEnableSendButton")
                    .Column<string>("FbLikesLayoutStyle", c => c.WithLength(255))
                    .Column<bool>("FbLikesShowFaces")
                    .Column<string>("FbLikesVerbToDisplay", c => c.WithLength(255))
                    .Column<string>("FbLikesFont", c => c.WithLength(255))
                );
            return 8;
        }

        public int UpdateFrom8()
        {
            SchemaBuilder.AlterTable("BookPartRecord", table => table.AddColumn<int>("WeekLikes", c => c.WithDefault(0).NotNull()));
            SchemaBuilder.AlterTable("BookPartRecord", table => table.AddColumn<int>("WeekViews", c => c.WithDefault(0).NotNull()));

            SchemaBuilder.AlterTable("AuthorProfilePartRecord", table => table.AddColumn<int>("WeekLikes", c => c.WithDefault(0).NotNull()));
            SchemaBuilder.AlterTable("AuthorProfilePartRecord", table => table.AddColumn<int>("TotalLikes", c => c.WithDefault(0).NotNull()));

            SchemaBuilder.AlterTable("NineSeasSettingsPartRecord", table => table.AddColumn<int>("WeekFavouriteBookId", c => c.WithDefault(0).NotNull()));
            SchemaBuilder.AlterTable("NineSeasSettingsPartRecord", table => table.AddColumn<int>("WeekMostViewedBookId", c => c.WithDefault(0).NotNull()));
            SchemaBuilder.AlterTable("NineSeasSettingsPartRecord", table => table.AddColumn<int>("WeekFavouriteAuthorId", c => c.WithDefault(0).NotNull()));
            SchemaBuilder.AlterTable("NineSeasSettingsPartRecord", table => table.AddColumn<int>("WeekRecommendedAuthorId", c => c.WithDefault(0).NotNull()));
            return 9;
        }
        public int UpdateFrom9()
        {
            SchemaBuilder.AlterTable("AuthorProfilePartRecord", table => table.AddColumn<DateTime>("CreatedUtc", c => c.Nullable()));

            return 10;
        }

        public int UpdateFrom10()
        {
            SchemaBuilder.AlterTable("AuthorProfilePartRecord", table => table.AddColumn<int>("BookCount", c => c.WithDefault(0).NotNull()));

            var authors = _authorProfileRepository.Fetch(a => true);

            foreach (var author in authors)
            {
                author.BookCount = _bookRepository.Count(b => b.AuthorId == author.Id);
                _authorProfileRepository.Update(author);
            }

            _authorProfileRepository.Flush();

            return 11;
        }

        public int UpdateFrom11()
        {
            SchemaBuilder.AlterTable("NineSeasSettingsPartRecord", table => table.AddColumn<DateTime>("LastWeekCalculationDate", c => c.Nullable()));

            return 12;
        }

        public int UpdateFrom12()
        {
            SchemaBuilder.AlterTable("AuthorProfilePartRecord", table => table.AddColumn<int>("BookCountWeek", c => c.WithDefault(0).NotNull()));

            SchemaBuilder.AlterTable("NineSeasSettingsPartRecord", table => table.AddColumn<int>("BookFavouriteWeek"));
            SchemaBuilder.AlterTable("NineSeasSettingsPartRecord", table => table.AddColumn<int>("BookFavourite"));
            SchemaBuilder.AlterTable("NineSeasSettingsPartRecord", table => table.AddColumn<int>("BookMostViewedWeek"));
            SchemaBuilder.AlterTable("NineSeasSettingsPartRecord", table => table.AddColumn<int>("BookMostViewed"));
            SchemaBuilder.AlterTable("NineSeasSettingsPartRecord", table => table.AddColumn<int>("AuthorFavouriteWeek"));
            SchemaBuilder.AlterTable("NineSeasSettingsPartRecord", table => table.AddColumn<int>("AuthorNewBookWeek"));

            _calculateTask.DoWork();
            return 13;
        }

        public int UpdateFrom13()
        {
            ContentDefinitionManager.AlterTypeDefinition("Book", t => t
                .WithPart(typeof(TagsPart).Name)
                );
            return 14;
        }

        public int UpdateFrom14()
        {
            ContentDefinitionManager.AlterTypeDefinition("Book", t => t
                .WithPart(typeof(CommonPart).Name)
                );

            var allBooks = _bookRepository.Fetch(b => true).ToList();

            var now = _clock.UtcNow;
            foreach (var book in allBooks)
            {
                var commonPartRecord = _commonPartRepository.Fetch(c => c.Id == book.Id).FirstOrDefault();

                if (commonPartRecord == null)
                {
                    commonPartRecord = new CommonPartRecord();
                    commonPartRecord.ContentItemRecord = book.ContentItemRecord;
                    commonPartRecord.OwnerId = book.AuthorId;
                    commonPartRecord.CreatedUtc = book.CreatedDate;
                    commonPartRecord.PublishedUtc = book.CreatedDate;
                    commonPartRecord.ModifiedUtc = now;

                    _commonPartRepository.Create(commonPartRecord);
                }
            }

            _commonPartRepository.Flush();

            return 15;
        }

        public int UpdateFrom15()
        {
            var categoryTaxonomyPart = _taxonomyImportService.CreateTaxonomy(Constants.Taxonomies.Categories);
            ContentDefinitionManager.AlterPartDefinition("BookPart", builder => builder
                .WithField(Constants.Taxonomies.Categories, cfg => cfg
                    .OfType("TaxonomyField")
                    .WithSetting("TaxonomyFieldSettings.AllowCustomTerms", "false")
                    .WithSetting("TaxonomyFieldSettings.SingleChoice", "false")
                    .WithSetting("TaxonomyFieldSettings.Required", "true")
                    .WithSetting("TaxonomyFieldSettings.Taxonomy", categoryTaxonomyPart.Name)));
            return 16;
        }

        public int UpdateFrom16()
        {
            var categories = "truyện ngắn, truyện dài tập, cuộc sống, truyện cổ, phiêu lưu, hài, kinh dị, viễn tưởng, kiếm hiệp tình yêu, học trò, nhật kí, trinh thám, thơ ca, tâm lý, sử học, khoa học, giới tính, gia đình, lãng mạng, tâm linh, nghệ thuật, tôn giáo, công nghệ, thời trang, khới nghiệp, du lịch, quản trị, y học, kinh tế, truyền thông, kiến trúc, điện ảnh, nấu ăn";
            var taxonomy = _taxonomyService.GetTaxonomyByName(Constants.Taxonomies.Categories);

            var regexObj = new Regex(@"\s*,\s*", RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace);

            foreach (var category in regexObj.Split(categories))
            {
                _taxonomyImportService.CreateTermFor(taxonomy, category.UppercaseFirst(), category.ToUrlFriendly());
            }
            return 17;
        }

        public int UpdateFrom17()
        {
            //SchemaBuilder.DropForeignKey("ChapterPartRecord", "Chapter_Book");

//            SchemaBuilder.AlterTable("BookPartRecord", table => table
//                .AddColumn<int>("Status", c => c.WithDefault(0))
//                );
//            SchemaBuilder.AlterTable("BookPartRecord", table => table.AddColumn<int>("ContentItemRecord_id"));

//            ContentDefinitionManager.AlterTypeDefinition("Book", t => t
//                    .WithPart(typeof(PublishLaterPart).Name)
//                    .Draftable());

//            SchemaBuilder.ExecuteSql(@"UPDATE NineSeas_BookPartRecord SET ContentItemRecord_id = Id");
//            // fix for records that don't have the version record
//            SchemaBuilder.ExecuteSql(@"
//                INSERT INTO Orchard_Framework_ContentItemVersionRecord (Number, Published, Latest, Data, ContentItemRecord_id)
//                SELECT 1, 1, 1, NULL, b.ContentItemRecord_id from NineSeas_BookPartRecord b
//	                WHERE NOT EXISTS(SELECT TOP(1) Id FROM Orchard_Framework_ContentItemVersionRecord V 
//		                        WHERE V.ContentItemRecord_id = b.ContentItemRecord_id AND V.Published = 1 
//				                ORDER BY V.Number DESC)");

//            SchemaBuilder.ExecuteSql(@"
//                UPDATE NineSeas_BookPartRecord
//                SET Id = (SELECT TOP(1) Id FROM Orchard_Framework_ContentItemVersionRecord V 
//		                WHERE V.ContentItemRecord_id = NineSeas_BookPartRecord.ContentItemRecord_id AND V.Published = 1 ORDER BY V.Number DESC)");
                        
//            SchemaBuilder.AlterTable("ChapterPartRecord", table => table.AddColumn<int>("ContentItemRecord_id"));

//            ContentDefinitionManager.AlterTypeDefinition("Chapter", t => t
//                    .WithPart(typeof(PublishLaterPart).Name)
//                    .Draftable());

//            SchemaBuilder.ExecuteSql(@"UPDATE NineSeas_ChapterPartRecord SET ContentItemRecord_id = Id");

//            // fix for records that don't have the version record
//            SchemaBuilder.ExecuteSql(@"
//                INSERT INTO Orchard_Framework_ContentItemVersionRecord (Number, Published, Latest, Data, ContentItemRecord_id)
//                SELECT 1, 1, 1, NULL, c.ContentItemRecord_id from NineSeas_ChapterPartRecord c
//	                WHERE NOT EXISTS(SELECT TOP(1) Id FROM Orchard_Framework_ContentItemVersionRecord V 
//		                        WHERE V.ContentItemRecord_id = c.ContentItemRecord_id AND V.Published = 1 
//				                ORDER BY V.Number DESC)");

            SchemaBuilder.ExecuteSql(@"
                UPDATE NineSeas_ChapterPartRecord
                SET Id = (SELECT TOP(1) Id FROM Orchard_Framework_ContentItemVersionRecord V 
		                WHERE V.ContentItemRecord_id = NineSeas_ChapterPartRecord.ContentItemRecord_id AND V.Published = 1 ORDER BY V.Number DESC)");


            return 18;
        }
    }
}